﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

using XianShang.Data;
using XianShang.Entity;
using XianShang.Common;

namespace XianShang.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {
        /// <summary>
        /// 檢查用戶名和密碼
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pass"></param>
        /// <returns></returns>
        public string CheckUserAndPass(string name, string pass)
        {
            DbParameter[] getPar = {
                                       DbHelper.MakeInParam("@account",(DbType)SqlDbType.NVarChar,200,name),
                                       DbHelper.MakeInParam("@password",(DbType)SqlDbType.NVarChar,100,pass)
                                   };
            string commandText = "SELECT * FROM [dbo].[XS_A_User] WHERE [account]=@account AND [password]=@password";
            return DbHelper.ExecuteScalarToStr(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據用戶名和密碼取得用戶信息
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public DataSet GetUserInfoByAccountAndPass(ShortUserInfo user)
        {
            DbParameter[] getPar = {
                                       DbHelper.MakeInParam("@account",(DbType)SqlDbType.NVarChar,200,user.UserName),
                                       DbHelper.MakeInParam("@password",(DbType)SqlDbType.NVarChar,100,user.UserPass)
                                   };
            string commandText = "SELECT * FROM [dbo].[XS_A_User] WHERE [account]=@account AND [password]=@password";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 取得用戶ID
        /// </summary>
        /// <returns></returns>
        public string GetUserAccountId(string name)
        {
            DbParameter getPar = DbHelper.MakeInParam("@account", (DbType)SqlDbType.NVarChar, 200, name);
                                  
            string commandText = "SELECT * FROM [dbo].[XS_A_User] WHERE [account]=@account";
            return DbHelper.ExecuteScalarToStr(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據用戶名取得用戶信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetUserAccountByUserName(string name)
        {
            DbParameter getPar = DbHelper.MakeInParam("@account", (DbType)SqlDbType.NVarChar, 200, name);

            string commandText = "SELECT * FROM [dbo].[XS_A_User] WHERE [account]=@account";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar).Tables[0];
        }
        /// <summary>
        /// 根據用戶ID取得用戶名信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetUserAccountInfo(string userId)
        {
            DbParameter getPar = DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, userId);

            string commandText = "SELECT * FROM [dbo].[XS_A_User] WHERE [id]=@id";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, getPar).Tables[0];
        }

        /// <summary>
        /// 增加一個用戶
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int AddNewUser(ShortUserInfo user)
        {
            DbParameter[] getPar = {
                                       DbHelper.MakeInParam("@account",(DbType)SqlDbType.NVarChar,200,user.UserName),
                                       DbHelper.MakeInParam("@password",(DbType)SqlDbType.NVarChar,100,user.UserPass),
                                       DbHelper.MakeInParam("@datetime",(DbType)SqlDbType.DateTime,DateTime.Now.ToString().Length,DateTime.Now)
                                   };
            string commandText="INSERT INTO [dbo].[XS_A_User]([Account],[Password],[System],[SysStatus],[LoginNum],[DateTime])VALUES(@account,@password,0,1,0,@datetime)";
            commandText += ";SELECT @@IDENTITY";

            return Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.Text, commandText, getPar), -1);
        }
        /// <summary>
        /// 增加一個超級用戶
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int AddNewSuperUser(ShortUserInfo user)
        {
            DbParameter[] getPar = {
                                       DbHelper.MakeInParam("@account",(DbType)SqlDbType.NVarChar,200,user.UserName),
                                       DbHelper.MakeInParam("@password",(DbType)SqlDbType.NVarChar,100,user.UserPass),
                                       DbHelper.MakeInParam("@datetime",(DbType)SqlDbType.DateTime,DateTime.Now.ToString().Length,DateTime.Now)
                                   };
            string commandText = "INSERT INTO [dbo].[XS_A_User]([Account],[Password],[System],[SysStatus],[LoginNum],[DateTime])VALUES(@account,@password,1,1,0,@datetime)";
            commandText += ";SELECT @@IDENTITY";

            return Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.Text, commandText, getPar), -1);
        }
        /// <summary>
        /// 取得所有用戶信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllUserInfo()
        {
            string commandText = "SELECT * FROM [dbo].[XS_A_User]";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        /// <summary>
        /// 取得所有用戶和關聯角色信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllUserOrRolesInfo()
        {
            string commandText = "SELECT";
            commandText += "[dbo].[XS_A_User].[Account], [dbo].[XS_A_Roles].[RolesName], ";
            commandText += "[dbo].[XS_A_UserRoles].[RolesId] , [dbo].[XS_A_UserRoles].[UserId] ";
            commandText += "FROM ";
            commandText += "[dbo].[XS_A_User] INNER JOIN [dbo].[XS_A_UserRoles] ON ";
            commandText += "[dbo].[XS_A_User].[ID] = [dbo].[XS_A_UserRoles].[UserId] INNER JOIN";
            commandText += "[dbo].[XS_A_Roles] ON [dbo].[XS_A_UserRoles].[RolesId] = [dbo].[XS_A_Roles].[ID]";

            return DbHelper.ExecuteDataset(CommandType.Text,commandText);
        }
        /// <summary>
        /// 根據用戶ID刪除用戶
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int DelUser(string userId)
        {
            DbParameter getPar = DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, userId);

            string commandText = "DELETE  FROM [dbo].[XS_A_User] WHERE [id]=@id AND [System]<>1";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據用戶名刪除用戶
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int DelUserByAccount(string account)
        {
            DbParameter getPar = DbHelper.MakeInParam("@account", (DbType)SqlDbType.NVarChar, 200, account);

            string commandText = "DELETE  FROM [dbo].[XS_A_User] WHERE [account]=@account AND [System]<>1";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據用戶ID更新用戶狀態
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int UpDateUserSysStatusById(string userId, string sts)
        {
            DbParameter[] getPar = {
                                       DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, userId),
                                       DbHelper.MakeInParam("@sysstatus",(DbType)SqlDbType.TinyInt,4,sts)
                                   };

            string commandText = "UPDATE [dbo].[XS_A_User]  SET[SysStatus]=@sysstatus WHERE [id]=@id AND [System]<>1";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據用戶名刪除用戶包括超級用戶
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int DelUserByAccountAndSuperAccount(string account)
        {
            DbParameter getPar = DbHelper.MakeInParam("@account", (DbType)SqlDbType.NVarChar, 200, account);

            string commandText = "DELETE  FROM [dbo].[XS_A_User] WHERE [account]=@account";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
        /// <summary>
        /// 根據用戶ID更新用戶密碼
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public int UpDateUserPasswordById(string userId, string pass)
        {
            DbParameter[] getPar = {
                                       DbHelper.MakeInParam("@id", (DbType)SqlDbType.Int, int.MaxValue, userId),
                                       DbHelper.MakeInParam("@password",(DbType)SqlDbType.NVarChar,100,pass)
                                   };

            string commandText = "UPDATE [dbo].[XS_A_User]  SET[password]=@password WHERE [id]=@id ";
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, getPar);
        }
    }
}
